CREATE PROCEDURE [dbo].[BAESearchEvents]
@SearchString VARCHAR(200),
@MatchType INT

AS

-- SeachString:  String of 1 or more search terms, all separated by spaces
-- MatchType: 0=match any, 1 =match all, 2 = exact match of entire expression only
-- Results are returned in order of relevance

DECLARE @i1 INT;
DECLARE @i2 INT;
DECLARE @Word VARCHAR(100);
DECLARE @Words TABLE (Word VARCHAR(100) NOT NULL);
DECLARE @Events TABLE (
    Meeting    VARCHAR(10),
    Title VARCHAR(60),
    Description text
)

INSERT INTO @Events
SELECT MEETING, TITLE, DESCRIPTION FROM Meet_Master
    WHERE WEB_ENABLED = '1' AND STATUS = 'A'  AND (BEGIN_DATE > GETDATE() OR END_DATE >= GETDATE())

DECLARE @WordCount AS integer;

SET NOCOUNT ON

-- Parse the SearchString to extract all words:

IF (@MatchType != 2)
    BEGIN
        SET @SearchString = ' ' + @SearchString  + ' ';
        SET @i1 = 1;

        WHILE (@i1 != 0)
            BEGIN
                SET @i2=CHARINDEX(' ', @SearchString, @i1+1)
                IF (@i2 != 0)
                    BEGIN
                      SET @Word = RTRIM(LTRIM(SUBSTRING(@SearchString, @i1+1, @i2-@i1)))
                      IF @Word != '' INSERT INTO @Words SELECT @Word
                    END
                SET @i1 = @i2
            END
        END
ELSE
    INSERT INTO @Words SELECT LTRIM(RTRIM(@SearchString))

-- Get the total # of words:

set @WordCount = (select count(*) from @Words)

-- Return Results in order of relevance:

SELECT a.MatchPct, T.*
FROM @Events AS T
INNER JOIN
(
    SELECT T.Meeting, COUNT(*) * 1.0 / @WordCount AS MatchPct
    FROM @Events T
    INNER JOIN
        @Words W on (' ' + LOWER(T.Title) + ' ' LIKE '%[^a-z]' + LOWER(Word) + '[^a-z]%')
        OR (' ' + Lower(Substring(T.Description,1,DATALENGTH(T.Description))) + ' ' LIKE '%[^a-z]' + LOWER(Word) + '[^a-z]%')
    GROUP BY T.Meeting
) a ON T.Meeting = a.Meeting
WHERE
    MatchPct = 1 or @MatchType <>1
ORDER BY
    MatchPct desc

GO